package org.web.base.helper.excel;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.web.base.domain.exception.ResultMessageEnum;
import org.web.base.domain.exception.ServiceException;
import org.web.base.domain.helper.ServiceExceptionHelper;
import org.web.base.helper.DateHelper;
import org.web.base.helper.StringHelper;

public class ExcelHelper {


    private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
    private static final DataFormatter dataFormatter = new DataFormatter();
    private static final SimpleDateFormat DateFormat = new SimpleDateFormat(DATE_FORMAT);

    private static final Map<Class, List<Field>> GlobleFieldsMap = new HashMap<>();

    public static <T> List<T> parseExcel(InputStream in, Class<T> clazz) throws Exception {
        return parseExcel(in, null, clazz, 1);
    }

    /**
     * 解析 Excel 文件，生成对象列表
     */
    public static <T> List<T> parseExcel(InputStream in, String sheetName, Class<T> clazz, int headRowNum) throws Exception {
        List<T> list = new ArrayList<>();
        Workbook workbook = new XSSFWorkbook(in);
        Sheet sheet = getSheet(workbook, sheetName, clazz);

        Row headerRow = sheet.getRow(headRowNum); // 假设第二行为属性名称
        List<String> attributeNames = getAttributeNames(headerRow);
        Map<String, Method> methodMap = getSetterMethods(clazz, attributeNames); // 根据属性名称获取 setter 方法，Map的size并不一定等于属性数量，因为有些属性可能没有对应的 setter 方法。
        List<Integer> validColumnIndexList = new ArrayList<>(); // 有效列索引, 提升解析效率。
        List<String> validAttributeNames = new ArrayList<>(); // 有效属性名称, 提升解析效率。与validColumnIndexList一一对应。
        for (int i = 0; i < attributeNames.size(); i++) {
            if (methodMap.containsKey(attributeNames.get(i))) {
                validColumnIndexList.add(i);
                validAttributeNames.add(attributeNames.get(i));
            }
        }
        for (int i = headRowNum + 1; i <= sheet.getLastRowNum(); i++) {
            T obj = clazz.newInstance();
            Row row = sheet.getRow(i);
            boolean isAllEmpty = true; // 所有列均为空，则跳过该行。
            for (int j = 0; j < validColumnIndexList.size(); j++) {
                int index = validColumnIndexList.get(j);
                if (!"".equals(String.valueOf(row.getCell(index)))) {// 遍历基于有效列索引的有效属性名称，提升解析效率。
                    isAllEmpty = false;
                    setCellValue(obj, methodMap, row.getCell(index), validAttributeNames.get(j));
                }
            }
            if (isAllEmpty) {
                break;
            } else {
                list.add(obj);
            }
        }
        return list;
    }

    /**
     * 获取 sheet，按类名或类的全限定名查找
     */
    private static <T> Sheet getSheet(Workbook workbook, String sheetName, Class<T> clazz) throws ServiceException {
        Sheet sheet = null;
        if (StringUtils.isNotBlank(sheetName)) {
            sheet = workbook.getSheet(sheetName);
        }
        if (sheet == null) {
            workbook.getSheet(clazz.getSimpleName());
        }
        if (sheet == null) {
            sheet = workbook.getSheet(clazz.getName());
        }
        if (sheet == null) {
            throw ServiceExceptionHelper.buildServiceException(ResultMessageEnum.DATA_RECORD_NOT_EXIST, "没有找到适合的sheet页。");
        }
        return sheet;
    }

    /**
     * 设置单元格的值
     */
    private static <T> void setCellValue(T obj, Map<String, Method> methodMap, Cell cell, String attributeName) throws Exception {
        Method method = methodMap.get(attributeName);
        if (method != null) {
            Object value = parseCellValue(cell, method.getParameterTypes()[0]);
            if (value != null) {
                try {
                    method.invoke(obj, value);
                } catch (Exception e) {
                    throw e;
                }
            }
        }
    }


    /**
     * 获取列名列表
     */
    private static List<String> getAttributeNames(Row row) {
        List<String> attributeNames = new ArrayList<>();
        for (int i = 0; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && StringUtils.isNotBlank(cell.getStringCellValue())) {
                attributeNames.add(cell.getStringCellValue());
            } else {
                attributeNames.add(null);
            }
        }
        return attributeNames;
    }


    private static Object parseCellValue(Cell cell, Class<?> paramType) throws Exception {
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return null;
        }

        switch (cell.getCellType()) {
            case NUMERIC:
                return parseNumericCell(cell, paramType);
            case BOOLEAN:
                return parseBooleanCell(cell, paramType);
            case STRING:
                return parseStringCell(cell, paramType);
            case FORMULA:
                return parseFormulaCell(cell, paramType);
            default:
                return parseStringValue(cell.toString(), paramType);
        }
    }

    private static Object parseNumericCell(Cell cell, Class<?> paramType) throws Exception {
        // 统一使用 DataFormatter 获取字符串值
        DataFormatter formatter = new DataFormatter();
        String stringValue = formatter.formatCellValue(cell).trim();

        // 如果是日期类型，特殊处理
        if (paramType == Date.class) {
            try{
                return DateHelper.parseDateWithMultipleFormats(stringValue);
            } catch (Exception e){
                return DateHelper.parseDateWithMultipleFormats(cell.toString());
            }
        }

        double numericValue = cell.getNumericCellValue();

        // 根据目标类型处理数值
        switch (paramType.getSimpleName()) {
            case "String":
                // 直接返回DataFormatter格式化后的字符串
                return stringValue;
            case "Integer":
            case "int":
                return (int) numericValue;
            case "Double":
            case "double":
                return numericValue;
            case "Long":
            case "long":
                return (long) numericValue;
            case "Float":
            case "float":
                return (float) numericValue;
            case "Byte":
            case "byte":
                return (byte) numericValue;
            case "Boolean":
            case "boolean":
                return numericValue != 0;
            // 删除Date case，因为已经在上面处理了
            default:
                if (paramType.isEnum()) {
                    int intValue = (int) numericValue;
                    Method valuesMethod = paramType.getMethod("values");
                    Object[] enumValues = (Object[]) valuesMethod.invoke(null);
                    if (intValue >= 0 && intValue < enumValues.length) {
                        return enumValues[intValue];
                    }
                    throw new IllegalArgumentException("枚举索引越界: " + intValue);
                }
                throw new ClassNotFoundException("不支持的数值类型: " + paramType.getSimpleName());
        }
    }

    private static Object parseStringCell(Cell cell, Class<?> paramType) throws Exception {
        String stringValue = cell.getStringCellValue().trim();
        return parseStringValue(stringValue, paramType);
    }

    private static Object parseStringValue(String cellValue, Class<?> paramType) throws Exception {
        if (cellValue == null || cellValue.isEmpty()) {
            return null;
        }

        if (paramType.isEnum()) {
            Method valueOfMethod = paramType.getMethod("valueOf", String.class);
            return valueOfMethod.invoke(null, cellValue);
        }

        try {
            switch (paramType.getSimpleName()) {
                case "String":
                    return cellValue;
                case "Integer":
                case "int":
                    if (cellValue.contains(".")) {
                        return (int) Double.parseDouble(cellValue);
                    }
                    return Integer.parseInt(cellValue);
                case "Double":
                case "double":
                    return Double.parseDouble(cellValue);
                case "Boolean":
                case "boolean":
                    return parseEnhancedBoolean(cellValue);
                case "Date":
                    // 保持原来的Date解析逻辑不变
                    return DateHelper.parseDateWithMultipleFormats(cellValue);
                case "Long":
                case "long":
                    if (cellValue.contains(".")) {
                        return (long) Double.parseDouble(cellValue);
                    }
                    return Long.parseLong(cellValue);
                case "Float":
                case "float":
                    return Float.parseFloat(cellValue);
                case "Byte":
                case "byte":
                    if (cellValue.contains(".")) {
                        return (byte) Double.parseDouble(cellValue);
                    }
                    return Byte.parseByte(cellValue);
                case "Character":
                    if (cellValue.length() == 1) {
                        return cellValue.charAt(0);
                    }
                    throw new IllegalArgumentException("Invalid Character value: " + cellValue);
                default:
                    throw new ClassNotFoundException("未知的数据类型: " + paramType.getSimpleName());
            }
        } catch (NumberFormatException e) {
            throw new IllegalArgumentException("无法将值 '" + cellValue + "' 转换为 " + paramType.getSimpleName(), e);
        }
    }

    private static Object parseBooleanCell(Cell cell, Class<?> paramType) throws Exception  {
        boolean booleanValue = cell.getBooleanCellValue();

        switch (paramType.getSimpleName()) {
            case "Boolean":
                return booleanValue;
            case "boolean":
                return booleanValue;
            case "String":
                return String.valueOf(booleanValue);
            case "Integer":
                return booleanValue ? 1 : 0;
            case "int":
                return booleanValue ? 1 : 0;
            default:
                return parseStringValue(String.valueOf(booleanValue), paramType);
        }
    }

    private static Object parseFormulaCell(Cell cell, Class<?> paramType) throws Exception {
        try {
            switch (cell.getCachedFormulaResultType()) {
                case NUMERIC:
                    return parseNumericCell(cell, paramType);
                case BOOLEAN:
                    return parseBooleanCell(cell, paramType);
                case STRING:
                    return parseStringCell(cell, paramType);
                default:
                    return parseStringValue(cell.toString(), paramType);
            }
        } catch (Exception e) {
            return parseStringValue(cell.toString(), paramType);
        }
    }

    private static boolean parseEnhancedBoolean(String value) {
        if ("1".equals(value) || "1.0".equals(value) || "是".equals(value) || "Y".equals(value.toUpperCase()) || "TRUE".equalsIgnoreCase(value)) {
            return true;
        }
        if ("0".equals(value) || "0.0".equals(value) || "否".equals(value) || "N".equals(value.toUpperCase()) || "FALSE".equalsIgnoreCase(value)) {
            return false;
        }
        return Boolean.parseBoolean(value);
    }


    /**
     * 获取 setter 方法映射
     */
    private static <T> Map<String, Method> getSetterMethods(Class<T> clazz, List<String> attributeNames) {
        Map<String, Method> methodMap = new HashMap<>();
        Method[] methods = clazz.getMethods();
        for (String attributeName : attributeNames) {
            if (StringUtils.isNotBlank(attributeName)) {
                String methodName = "set" + StringHelper.initUpper(attributeName);
                for (Method method : methods) {
                    if (method.getName().equals(methodName) && method.getParameterTypes().length == 1) {
                        methodMap.put(attributeName, method);
                    }
                }
            }
        }
        return methodMap;
    }

    /**
     * 复制 sheet
     */
    public static Sheet copySheet(Workbook workbook, String sourceSheetName, String destinationSheetName) throws ServiceException {
        Sheet originalSheet = workbook.getSheet(sourceSheetName);
        Sheet newSheet = workbook.createSheet(destinationSheetName);

        for (int i = 0; i <= originalSheet.getLastRowNum(); i++) {
            Row originalRow = originalSheet.getRow(i);
            Row newRow = newSheet.createRow(i);

            if (originalRow != null) {
                for (int j = 0; j < originalRow.getLastCellNum(); j++) {
                    Cell originalCell = originalRow.getCell(j);
                    Cell newCell = newRow.createCell(j);
                    if (originalCell != null) {
                        copyCell(originalCell, newCell);
                    }
                }
            }
        }
        return newSheet;
    }

    /**
     * 复制单元格
     */
    public static void copyCell(Cell sourceCell, Cell destinationCell) {
        switch (sourceCell.getCellType()) {
            case NUMERIC:
                destinationCell.setCellValue(sourceCell.getNumericCellValue());
                break;
            case STRING:
                destinationCell.setCellValue(sourceCell.getStringCellValue());
                break;
            case BOOLEAN:
                destinationCell.setCellValue(sourceCell.getBooleanCellValue());
                break;
            case FORMULA:
                destinationCell.setCellFormula(sourceCell.getCellFormula());
                break;
            case BLANK:
                destinationCell.setBlank();
                break;
            default:
                destinationCell.setCellValue(sourceCell.toString());
                break;
        }
    }

    /**
     * 将 List<String> 的内容写入指定Sheet的指定行中
     *
     * @param dataList 要写入的字符串列表
     * @param sheet    目标Sheet
     * @param rowIndex 指定写入的行号（0开始计数）
     */
    public static void writeRow(Sheet sheet, int rowIndex, List<String> dataList) {
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
        }
        for (int i = 0; i < dataList.size(); i++) {
            Cell cell = row.getCell(i);
            if (cell == null) {
                cell = row.createCell(i);
            }
            cell.setCellValue(dataList.get(i));
        }
    }
}
